package com.link2room.rodimus.service.impl;

import com.link2room.common.dao.ICommonDao;
import com.link2room.common.jpa.result.QueryResult;
import com.link2room.common.util.DateUtil;
import com.link2room.rodimus.dto.param.manager.FansReportManagerParamDto;
import com.link2room.rodimus.dto.param.manager.PaymentReportManagerParamDto;
import com.link2room.rodimus.mapper.*;
import com.link2room.rodimus.service.IReportService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by VegetaKo on 2016/12/19.
 */
@Service("reportService")
public class ReportServiceImpl implements IReportService {

    @Resource
    private ICommonDao commonDao;


    @Override
    public QueryResult<FansReprotMapper> getFansReportBySalesman(FansReportManagerParamDto fansReportManagerParamDto) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeFansReportBySalesmanSQL(fansReportManagerParamDto,objects);
        String countSQL = this.makeFansReporyBySalesmanCountSQL(fansReportManagerParamDto,countObjects);
        QueryResult<FansReprotMapper> rankingListDtoQueryResult=commonDao.findByNative(querySQL,countSQL,objects,countObjects, FansReprotMapper.class, 1, Integer.MAX_VALUE);
        return  rankingListDtoQueryResult;
    }


    @Override
    public QueryResult<FansReprotMapper> getFansReportByHotel(FansReportManagerParamDto fansReportManagerParamDto) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeFansReportByHotelSQL(fansReportManagerParamDto,objects);
        String countSQL = this.makeFansReporyByHotelCountSQL(fansReportManagerParamDto,countObjects);
        QueryResult<FansReprotMapper> rankingListDtoQueryResult=commonDao.findByNative(querySQL,countSQL,objects,countObjects, FansReprotMapper.class, 1, Integer.MAX_VALUE);
        return  rankingListDtoQueryResult;
    }

    @Override
    public QueryResult<PaymentReportMapper> getPaymentReportBySalesman(PaymentReportManagerParamDto paymentReportManagerParamDto) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makePaymentReportBySalesmanSQL(paymentReportManagerParamDto,objects);
        String countSQL = this.makePaymentReporyBySalesmanCountSQL(paymentReportManagerParamDto,countObjects);
        QueryResult<PaymentReportMapper> paymentReportMapperQueryResult=commonDao.findByNative(querySQL,countSQL,objects,countObjects, PaymentReportMapper.class, 1, Integer.MAX_VALUE);
        return  paymentReportMapperQueryResult;
    }

    @Override
    public QueryResult<PaymentReportMapper> getPaymentReportByHotel(PaymentReportManagerParamDto paymentReportManagerParamDto) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makePaymentReportByHotelSQL(paymentReportManagerParamDto,objects);
        String countSQL = this.makePaymentReporyByHotelCountSQL(paymentReportManagerParamDto,countObjects);
        QueryResult<PaymentReportMapper> rankingListDtoQueryResult=commonDao.findByNative(querySQL,countSQL,objects,countObjects, PaymentReportMapper.class, 1, Integer.MAX_VALUE);
        return  rankingListDtoQueryResult;
    }

    @Override
    public QueryResult<MoonCakeSaleSummaryMapper> getMoonCakeSaleSummary(String startDate) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeMoonCakeSaleSummarySQL("",objects);
        String countSQL = this.makeMoonCakeSaleSummaryCountSQL("",countObjects);
        QueryResult<MoonCakeSaleSummaryMapper> moonCakeSaleSummaryMapperQueryResult = commonDao.findByNative(querySQL,countSQL,objects,countObjects, MoonCakeSaleSummaryMapper.class, 1, Integer.MAX_VALUE);
        return   moonCakeSaleSummaryMapperQueryResult;
    }

    @Override
    public QueryResult<MoonCakeSaleDetailMapper> getMoonCakeSaleDetail(String startDate) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeMoonCakeSaleDetailSQL("",objects);
        String countSQL = this.makeMoonCakeSaleDetailCountSQL("",countObjects);
        QueryResult<MoonCakeSaleDetailMapper> moonCakeSaleDetailMapperQueryResult = commonDao.findByNative(querySQL,countSQL,objects,countObjects, MoonCakeSaleDetailMapper.class, 1, Integer.MAX_VALUE);
        return   moonCakeSaleDetailMapperQueryResult;
    }

    @Override
    public  QueryResult<MoonCakeExpressMapper> getMoonCakeExpress(String startDate) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeMoonCakeExpressSQL("",objects);
        String countSQL = this.makeMoonCakeExpressCountSQL("",countObjects);
        QueryResult<MoonCakeExpressMapper>  moonCakeExpressMapperQueryResult = commonDao.findByNative(querySQL,countSQL,objects,countObjects, MoonCakeExpressMapper.class, 1, Integer.MAX_VALUE);
        return   moonCakeExpressMapperQueryResult;
    }


    private String makeFansReportBySalesmanSQL(  FansReportManagerParamDto fansReportManagerParamDto,List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  sm.lhotel_group_code lhotelGroupCode," +
                "  sm.lhotel_code lhotelCode," +
                "  sm.salesman_name salesmanName," +
                "  count(1) AS count," +
                "  DATE_FORMAT( sm.create_datetime,'%Y-%m-%d') AS bizDate"+
                "  FROM" +
                "  salesman_member  sm " );
        sb.append( " WHERE  sm.lhotel_group_code = ?1" );
        objects.add(fansReportManagerParamDto.getLhotelGroupCode() );
        sb.append( " AND sm.create_datetime >= ?2" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( " AND sm.create_datetime <= ?3" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( " AND sm.salesman_name IS NOT NULL " );
        sb.append( " GROUP BY  sm.salesman_no, DATE_FORMAT( sm.create_datetime,'%Y-%m-%d')" );
        sb.append( " ORDER BY  sm.lhotel_code ,sm.salesman_no" );
        return sb.toString();
    }

    private String makeFansReporyBySalesmanCountSQL(FansReportManagerParamDto fansReportManagerParamDto, List objects)throws Exception{

        StringBuffer sb=new StringBuffer();
        sb.append("SELECT count(1) FROM ( " );
        sb.append("SELECT " +
                "  count(1) count" +
                "  FROM" +
                "  salesman_member  sm " );
        sb.append( " WHERE  sm.lhotel_group_code >= ?1" );
        objects.add(fansReportManagerParamDto.getLhotelGroupCode() );
        sb.append( " AND sm.create_datetime >= ?2" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( " AND sm.create_datetime <= ?3" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( " AND sm.salesman_name IS NOT NULL " );
        sb.append( " GROUP BY  sm.salesman_no, DATE_FORMAT( sm.create_datetime,'%Y-%m-%d')" );
        sb.append( " ) AS a" );
        return sb.toString();
    }

    private String makeFansReportByHotelSQL(  FansReportManagerParamDto fansReportManagerParamDto,List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  sm.lhotel_group_code lhotelGroupCode," +
                "  sm.lhotel_code lhotelCode," +
                "  count(1) AS count," +
                "  DATE_FORMAT( sm.create_datetime,'%Y-%m-%d') AS bizDate"+
                "  FROM" +
                "  salesman_member  sm " );
        sb.append( " WHERE  sm.lhotel_group_code = ?1" );
        objects.add(fansReportManagerParamDto.getLhotelGroupCode() );
        sb.append( " AND sm.create_datetime >= ?2" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( " AND sm.create_datetime <= ?3" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( " GROUP BY  sm.lhotel_code, DATE_FORMAT( sm.create_datetime,'%Y-%m-%d')" );
        sb.append( " ORDER BY  sm.lhotel_code " );
        return sb.toString();
    }

    private String makeFansReporyByHotelCountSQL(FansReportManagerParamDto fansReportManagerParamDto, List objects)throws Exception{

        StringBuffer sb=new StringBuffer();
        sb.append("SELECT count(1) FROM ( " );
        sb.append("SELECT " +
                "  count(1) count" +
                "  FROM" +
                "  salesman_member  sm " );
        sb.append( " WHERE  sm.lhotel_group_code >= ?1" );
        objects.add(fansReportManagerParamDto.getLhotelGroupCode() );
        sb.append( " AND sm.create_datetime >= ?2" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( " AND sm.create_datetime <= ?3" );
        objects.add( DateUtil.format(fansReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( " GROUP BY  sm.lhotel_code, DATE_FORMAT( sm.create_datetime,'%Y-%m-%d')" );
        sb.append( " ) AS a" );
        return sb.toString();
    }

    private String makePaymentReportBySalesmanSQL(PaymentReportManagerParamDto paymentReportManagerParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  IFNULL(sp.lhotel_code,'total') AS lhotelCode," +
                "  IFNULL(sp.salesman_name,'total') AS salesmanName," +
                "  IFNULL(sp.audited_date,'2999-01-01') AS bizDate," +
                "  SUM(IF(sp.pay_type='MEMBER',`total`,0)) AS member," +
                "  SUM(IF(sp.pay_type='RESERVATION',`total`,0)) AS reservation,"+
                "  SUM(sp.total) AS total" +
                "  FROM  " +
                "  salesman_payment  sp " );
        sb.append("  WHERE  sp.lhotel_group_code = ?1" );
        objects.add(paymentReportManagerParamDto.getLhotelGroupCode() );
        sb.append( "  AND sp.audited_date >= ?2" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( "  AND sp.audited_date <= ?3" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( "  AND sp.salesman_name IS NOT NULL " );
        sb.append( "  AND sp.pay_sta = 'PAID' " );
        sb.append( "  GROUP BY sp.salesman_no,sp.audited_date WITH ROLLUP" );
        return sb.toString();
    }

    private String makePaymentReporyBySalesmanCountSQL(PaymentReportManagerParamDto paymentReportManagerParamDto, List objects)throws Exception{

        StringBuffer sb=new StringBuffer();
        sb.append("SELECT count(1) FROM ( " );
        sb.append("SELECT " +
                "  count(1) count" +
                "  FROM" +
                "  salesman_payment  sp " );
        sb.append( " WHERE  sp.lhotel_group_code >= ?1" );
        objects.add(paymentReportManagerParamDto.getLhotelGroupCode() );
        sb.append( " AND sp.audited_date >= ?2" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( " AND sp.audited_date <= ?3" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( " AND sp.salesman_name IS NOT NULL " );
        sb.append( " AND sp.pay_sta = 'PAID' " );
        sb.append( " GROUP BY  sp.salesman_no, sp.audited_date " );
        sb.append( " ) AS a" );
        return sb.toString();
    }

    private String makePaymentReportByHotelSQL(PaymentReportManagerParamDto paymentReportManagerParamDto,List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  IFNULL(sp.lhotel_code,'total') AS lhotelCode," +
                "  IFNULL(sp.audited_date,'2999-01-01') AS bizDate," +
                "  SUM(IF(sp.pay_type='MEMBER',`total`,0)) AS member," +
                "  SUM(IF(sp.pay_type='RESERVATION',`total`,0)) AS reservation,"+
                "  SUM(sp.total) AS total" +
                "  FROM  " +
                "  salesman_payment  sp " );
        sb.append("  WHERE  sp.lhotel_group_code = ?1" );
        objects.add(paymentReportManagerParamDto.getLhotelGroupCode() );
        sb.append( "  AND sp.audited_date >= ?2" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( "  AND sp.audited_date <= ?3" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( "  AND sp.salesman_name IS NOT NULL " );
        sb.append( "  AND sp.pay_sta = 'PAID' " );
        sb.append( "  GROUP BY sp.lhotel_code,sp.audited_date WITH ROLLUP" );
        return sb.toString();
    }

    private String makePaymentReporyByHotelCountSQL(PaymentReportManagerParamDto paymentReportManagerParamDto, List objects)throws Exception{

        StringBuffer sb=new StringBuffer();
        sb.append("SELECT count(1) FROM ( " );
        sb.append("SELECT " +
                "  count(1) count" +
                "  FROM" +
                "  salesman_payment  sp " );
        sb.append( " WHERE  sp.lhotel_group_code >= ?1" );
        objects.add(paymentReportManagerParamDto.getLhotelGroupCode() );
        sb.append( " AND sp.audited_date >= ?2" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getStartDate(),DateUtil.webFormat )+" 00:00:00");
        sb.append( " AND sp.audited_date <= ?3" );
        objects.add( DateUtil.format(paymentReportManagerParamDto.getEndDate(),DateUtil.webFormat )+" 23:59:59");
        sb.append( " AND sp.salesman_name IS NOT NULL " );
        sb.append( " AND sp.pay_sta = 'PAID' " );
        sb.append( " GROUP BY  sp.salesman_no, sp.audited_date " );
        sb.append( " ) AS a" );
        return sb.toString();
    }

    private String makeMoonCakeSaleSummarySQL(String startDate ,List objects) throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT DATE_FORMAT( NOW(),'%Y-%m-%d') as bizDate,zz.name,bb.total_count as totalCount FROM "+
                   " ( SELECT aa.salesman_no,sb.`salesman_name` , aa.total_count,sb.organization_code FROM "+
                   "   (SELECT SUM(mop.count) total_count, mob.salesman_no "+
                   "    FROM `ark_member_product`.`mall_order_base` mob,`ark_member_product`.`mall_order_price` mop "+
                   "    WHERE mob.`order_code` =mop.`order_code` "+
                   "      AND (mop.`good_code` ='MOONCAKE_01' OR mop.`good_code` ='MOONCAKE_02') "+
                   "      AND mob.`money_pay_sta`='PAID' GROUP BY mob.`salesman_no` "+
                   "    ) aa "+
                   " LEFT JOIN `ark_member_product`.`salesman_base` sb ON  aa.`salesman_no` =sb.`salesman_no` "+
                   " ) bb "+
                   " LEFT JOIN `ark_sideswipe_product`.`organization` zz ON zz.`code` =bb.`organization_code` "+
                   " GROUP BY zz.name " );
     /*   objects.add("QINGTENG" );
        sb.append( "  AND mob.create_datetime <= ?2" );
        objects.add(startDate+" 23:49:59");
        sb.append( "  AND mob.salesman_name IS NOT NULL " );
        sb.append( "  AND mob.money_pay_sta = 'PAID' " );
        sb.append( "  GROUP BY sp.lhotel_code,sp.audited_date WITH ROLLUP" );*/
        return sb.toString();
    }

    private String makeMoonCakeSaleSummaryCountSQL(String startDate,List objects ) throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT COUNT(1) FROM "+
                " ( SELECT aa.salesman_no,sb.`salesman_name` , aa.totalcount,sb.organization_code FROM "+
                "   (SELECT SUM(mop.count) totalcount, mob.salesman_no "+
                "    FROM `ark_member_product`.`mall_order_base` mob,`ark_member_product`.`mall_order_price` mop "+
                "    WHERE mob.`order_code` =mop.`order_code` "+
                "      AND (mop.`good_code` ='MOONCAKE_01' OR mop.`good_code` ='MOONCAKE_02') "+
                "      AND mob.`money_pay_sta`='PAID' GROUP BY mob.`salesman_no` "+
                "    ) aa "+
                " LEFT JOIN `ark_member_product`.`salesman_base` sb ON  aa.`salesman_no` =sb.`salesman_no` "+
                " ) bb "+
                " LEFT JOIN `ark_sideswipe_product`.`organization` zz ON zz.`code` =bb.`organization_code` "
                );
             return sb.toString();
    }
    String makeMoonCakeSaleDetailSQL(String startDate ,List objects) throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT dd.buy_date AS buyDate,dd.order_code AS orderCode,dd.salesman_no AS salesmanNo,dd.ppname,dd.name,dd.salesman_name as salesmanName "+
                    ",mgb.`name` AS productName,dd.total_count as totalCount,128.00 AS price, total_count*128.00 AS prices  FROM "+
                  "  (SELECT pp.`name` ppname,cc.* FROM "+
                  "     (SELECT bb.buy_date,bb.order_code,bb.salesman_no,zz.name,bb.salesman_name,bb.`good_code`,bb.total_count,zz.p_code FROM "+
                  "        (SELECT aa.buy_date,aa.order_code,aa.salesman_no,sb.`salesman_name` , aa.total_count,sb.organization_code,aa.`good_code` FROM " +
                  "           (SELECT  DATE_FORMAT( mob.`create_datetime`,'%Y-%m-%d') buy_date,mob.`order_code`,mop.count total_count, mob.salesman_no,mop.`good_code` "+
                  "                 FROM `ark_member_product`.`mall_order_base` mob,`ark_member_product`.`mall_order_price` mop "+
                  "                 WHERE mob.`order_code` =mop.`order_code` "+
                  "                       AND (mop.`good_code` ='MOONCAKE_01' OR mop.`good_code` ='MOONCAKE_02') "+
                  "                       AND mob.`money_pay_sta`='PAID' "+
                  "            ) aa "+
                  "            LEFT JOIN `ark_member_product`.`salesman_base` sb ON  aa.`salesman_no` =sb.`salesman_no` "+
                  "         ) bb "+
                  "           LEFT JOIN `ark_sideswipe_product`.`organization` zz ON zz.`code` =bb.`organization_code` "+
                  "      )cc "+
                  "       LEFT JOIN `ark_sideswipe_product`.`organization` pp ON pp.`code` =cc.`p_code` "+
                  "   )dd "+
                  "    LEFT JOIN `ark_member_product`.`mall_good_base` mgb ON mgb.`good_code` =dd.`good_code` "+
                  "ORDER BY dd.buy_date DESC "
        );
        return sb.toString();
    }
    String makeMoonCakeSaleDetailCountSQL(String startDate ,List objects) throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT COUNT(1) FROM "+
                        "  (SELECT pp.`name` ppname,cc.* FROM "+
                        "     (SELECT bb.buy_date,bb.order_code,bb.salesman_no,zz.name,bb.salesman_name,bb.`good_code`,bb.total_count,zz.p_code FROM "+
                        "        (SELECT aa.buy_date,aa.order_code,aa.salesman_no,sb.`salesman_name` , aa.total_count,sb.organization_code,aa.`good_code` FROM " +
                        "           (SELECT  DATE_FORMAT( mob.`create_datetime`,'%Y-%m-%d') buy_date,mob.`order_code`,mop.count total_count, mob.salesman_no,mop.`good_code` "+
                        "                 FROM `ark_member_product`.`mall_order_base` mob,`ark_member_product`.`mall_order_price` mop "+
                        "                 WHERE mob.`order_code` =mop.`order_code` "+
                        "                       AND (mop.`good_code` ='MOONCAKE_01' OR mop.`good_code` ='MOONCAKE_02') "+
                        "                       AND mob.`money_pay_sta`='PAID' "+
                        "            ) aa "+
                        "            LEFT JOIN `ark_member_product`.`salesman_base` sb ON  aa.`salesman_no` =sb.`salesman_no` "+
                        "         ) bb "+
                        "           LEFT JOIN `ark_sideswipe_product`.`organization` zz ON zz.`code` =bb.`organization_code` "+
                        "      )cc "+
                        "       LEFT JOIN `ark_sideswipe_product`.`organization` pp ON pp.`code` =cc.`p_code` "+
                        "   )dd "+
                        "    LEFT JOIN `ark_member_product`.`mall_good_base` mgb ON mgb.`good_code` =dd.`good_code` "+
                        "ORDER BY dd.buy_date DESC "
        );
        return sb.toString();
    }
    String makeMoonCakeExpressSQL(String startDate,List objects) throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT express_no AS expressNo,good_name AS goodName,good_amount as goodAmount,receiver_name AS receiverName "+
                     ", CONCAT(mobile,'_') AS mobile ,address,'',CONCAT(third_express_no,'_')  AS thirdExpressNo  "+
                     "FROM  `ark_member_product`.`express_base` eb "+
                     "WHERE  (eb.`good_code` ='MOONCAKE_01' OR eb.`good_code` ='MOONCAKE_02') "+
                     "ORDER BY  express_no DESC"
        );
        return sb.toString();
    }
    String makeMoonCakeExpressCountSQL(String startDate,List objects) throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT COUNT(1) "+
                "FROM  `ark_member_product`.`express_base` eb "+
                "WHERE  (eb.`good_code` ='MOONCAKE_01' OR eb.`good_code` ='MOONCAKE_02') "+
                "ORDER BY  express_no DESC"
        );
        return sb.toString();
    }

}
